﻿using System;
using System.Collections;
using System.Collections.Generic;
using System.Text;
using System.Data;
using System.Data.SqlClient;

namespace sgsBase
{

	/// <summary>
	/// Base class for all database interface classes.
	/// </summary>
    [Serializable]public class DbViewObject 
    {
        [NonSerialized]protected DbInterface _dbi; // should be re-created between sessions.

		//protected List<DbColumn> _columns = new List<DbColumn>(); // holds the list of column definitions
		protected DbColumns _columns = new DbColumns();
		protected DataTable _dt; // holds the result set that underpins the class. 
		protected string _dbconn_name = ""; // name of the databse connection string. 
		protected string _table;

		protected string _sortby = "";
        protected string _where = "";
		protected string _andClause = ""; //And & Or clauses would be used in addition to normal field value checks.
		protected string _orClause = "";

		public bool IgnoreError = false;
		public bool Success = false;
        //protected int m_pkCount = 0;
		// used for looping through items of a result set.
		protected int _curRow;
		protected string m_andor = " or ";
		protected List<string> m_andlist = new List<string>();
		protected List<string> m_orlist = new List<string>();
		protected List<DbConditionalView> m_AltViews = new List<DbConditionalView>();
		public string Sql;

		/// <summary>
		/// Class Constructor
		/// </summary>
        public DbViewObject()
        {
        }

		/// <summary>
		/// Class Destructor
		/// </summary>
		~DbViewObject()
		{
			//_columns.Clear();
			_columns = null;
		}

        #region Properties

		/// <summary>
		/// Returns the DbInterface class used in calling the database.
		/// </summary>
		/// This class could be handy for running independent queries without re-creating the object.
		public DbInterface Interface
		{
			get { return dbi; }
		}

		/// <summary>
		/// The Database connection string.
		/// </summary>
		/// Not really the databse, but I recommned naming the conneciton string the same as the database.
		public string Database
		{
			get { return _dbconn_name; }
			set { _dbconn_name = value; }
		}

		/// <summary>
		/// Table to be written to or queried
		/// </summary>
        public string Table
        {
            get 
			{ 
				return _table;
			}
            set { _table = value; }
        }

		// where clause assigned for query
		/// <summary>
		/// Used to manually assign the where statement. 
		/// </summary>
		/// Requires actual column names. You might have a class with the property UserName, which maps to the database
		/// field [user_name]. In the WhereClause, you would write "user_name='sample'"
        public string WhereClause
        {
            get{return _where;}
            set{_where = value;}
        }

		/// <summary>
		/// Adds a partial where clause statment that must be true.
		/// </summary>
		public string AndClause
		{
			get { return _andClause; }
			set { _andClause = value; }
		}

		/// <summary>
		/// Add a partial where clause that may be true even if he main clause is not.
		/// </summary>
		public string OrClause
		{
			get { return _orClause; }
			set { _orClause = value; }
		}

		/// <summary>
		/// Returns the list of the databse columns mapped in the derived class.
		/// </summary>
		//public List<DbColumn> Columns
		//{
		//    get { return _columns; }
		//    // Danger Will Robinson! this should only be used by the copy function. 
		//    set { _columns = value; }
		//}
		public DbColumns Columns
		{
			get { return _columns; }
			// Danger Will Robinson! this should only be used by the copy function. 
			set { _columns = value; }
		}

		/// <summary>
		/// Creates an Order By clause. This must be true SQL at this point. e.g. "user_name desc"
		/// </summary>
        public string SortBy
        {
            get { return _sortby; }
            set { _sortby = value; }
        }

		/// <summary>
		/// SAme as SortBy
		/// </summary>
		public string OrderBy
		{
			set { SortBy = value; }
		}

		// forces the system to use And in LIKE statements
		/// <summary>
		/// 
		/// </summary>
		public bool AndOnly
		{
			set
			{
				if (value)
					m_andor = " and ";
				else
					m_andor = " or ";
			}
		}
		#endregion

		#region Protected Properties
		/// <summary>
		/// Return the DbInterface connection class
		/// </summary>
		protected DbInterface dbi
		{
			get
			{
				if (_dbi == null)
					_dbi = new DbInterface(_dbconn_name);

				return _dbi;
			}
		}

		#endregion

		#region Table Columns
		/// <summary>
        /// Adds name only, assuming that data type is VarChar, is not a key, and is not auto-increment
        /// </summary>
        /// <param name="name"></param>
		//public void AddColumn(string name)
		//{
		//    DbColumn dbc = new DbColumn(name);
		//    _columns.Add(dbc);
		//}
		//public void AddColumn(string name, bool Nullable)
		//{
		//    DbColumn dbc = new DbColumn(name);
		//    dbc.Nullable = Nullable;
		//    _columns.Add(dbc);
		//}
		//public void AddColumn(string name, SqlDbType dbType)
		//{
		//    DbColumn dbc = new DbColumn(name, dbType);
		//    _columns.Add(dbc);
		//}
		//public void AddColumn(string name, SqlDbType dbType, bool Nullable)
		//{
		//    DbColumn dbc = new DbColumn(name, dbType);
		//    dbc.Nullable = Nullable;
		//    _columns.Add(dbc);
		//}
		//public void AddColumn(string name, SqlDbType dbType, bool isKey, bool autoIncrement)
		//{
		//    DbColumn dbc = new DbColumn(name, dbType, isKey, autoIncrement);
		//    if (isKey)
		//        m_pkCount++;
		//    _columns.Add(dbc);
		//}
		//public void AddColumn(string name, SqlDbType dbType, bool isKey, bool autoIncrement, string value)
		//{
		//    DbColumn dbc = new DbColumn(name, dbType, isKey, autoIncrement);
		//    if (isKey)
		//        m_pkCount++; 
		//    dbc.Value = value;
		//    _columns.Add(dbc);
		//}
		//public void AddColumn(DbColumn dbc)
		//{
		//    _columns.Add(dbc);
		//    if (dbc.PrimaryKey)
		//        m_pkCount++;
		//}
		#endregion
		/// <summary>
		/// Move the current row to a random row in the result set.
		/// </summary>
		public void Random()
		{
			if (EoF())
				return;
			Random rnd = new Random();

			_curRow = rnd.Next(_dt.Rows.Count) - 1;
			MoveNext();
		}
        /// <summary>
        /// AddValue Adds the value given to the specific database column
        /// </summary>
        /// <param name="ColumnName">The datbase column name.</param>
        /// <param name="Value">The value to set the column to.</param>
		//public void AddValue(string ColumnName, string Value)
		//{
		//    DbColumn dbc = _columns.Find(delegate(DbColumn db) { return db.ColumnName == ColumnName;});

		//    if (dbc != null)
		//    {
		//        dbc.Value = Value;
		//    }
		//}
		//public void AddValue(string ColumnName, int Value)
		//{
		//    DbColumn dbc = _columns.Find(delegate(DbColumn db) { return db.ColumnName == ColumnName; });

		//    if (dbc != null)
		//    {
		//        dbc.Value = Value.ToString();
		//    }
		//}
        public void ClearValues()
        {
            _columns.ClearValues();
            _sortby = "";
        }

		public DataTable DataTable
		{
			get { return _dt; }
		}


		//public void FromFile(string filename)
		//{
		//    CSVFile cf = new CSVFile();
		//    cf.HasHeaderRow = true;
		//    m_dt = cf.Import(filename);
		//    MoveFirst();
		//}

		//public void ToFile(string filename)
		//{
		//    CSVFile cf = new CSVFile();
		//    cf.HasHeaderRow = true;
		//    cf.ExportToFile(m_dt, filename);
		//}

		/// <summary>
		/// Allows for a filter class to come in and use its own method of removing records 
		/// from the resulting dataset.
		/// </summary>
		/// 
		public virtual void FilterData()
		{
		}

		/// <summary>
		/// Copy the object over to a new instance of the same class.
		/// </summary>
		/// <returns></returns>
		public object Copy()
		{
			DbViewObject dvo = (DbViewObject)this.MemberwiseClone();
			dvo.Columns = _columns.Copy();
			return dvo;
		}

		#region datbase Column Value Conversions
		//public bool valid(int col)
		//{
		//    return (_columns.Count > col);
		//}
		//public string StringAt(int col)
		//{
		//    return valid(col) ? _columns[col].Value: "";
		//}

		//public int IntAt(int col)
		//{
		//    return valid(col) ? (int)_columns[col].ValueInt: 0;
		//}

		//public long LongAt(int col)
		//{
		//    return valid(col) ?  _columns[col].ValueLong : 0;
		//}
		
		//public bool BoolAt(int col)
		//{
		//    return valid(col) ? _columns[col].ValueBool : false ;
		//}

		//public decimal DecimalAt(int col)
		//{
		//    return  valid(col) ? _columns[col].ValueDecimal : 0;
		//}

		//public double DoubleAt(int col)
		//{
		//    return  valid(col) ? _columns[col].ValueDouble: 0;
		//}

		//public DateTime DateAt(int col)
		//{
		//    return valid(col) ? _columns[col].ValueDate : DateTime.MinValue;
		//}

		//public DateTime DateTimeAt(int col)
		//{
		//    return  valid(col) ? _columns[col].ValueDate : DateTime.MinValue;
		//}

		//public DateTime DateTimeTSAt(int col)
		//{
		//    return  valid(col) ? _columns[col].ValueDateTime : DateTime.MinValue;
		//}
		//public void SetValAt(int col, int val)
		//{
		//    if (valid(col))
		//        _columns[col].ValueInt = val;
		//}
		//public void SetValAt(int col, long val)
		//{
		//    if (valid(col))
		//        _columns[col].ValueLong = val;
		//}
		//public void SetValAt(int col, string val)
		//{
		//    if (valid(col))
		//        _columns[col].Value = val;
		//}
		//public void SetValAt(int col, decimal val)
		//{
		//    if (valid(col))
		//        _columns[col].ValueDecimal = val;
		//}
		//public void SetValAt(int col, double val)
		//{
		//    if (valid(col))
		//        _columns[col].ValueDouble = val;
		//}
		//public void SetValAt(int col, DateTime val, bool IsTS)
		//{
		//    if (!valid(col))
		//        return;

		//    if (val.Year < 1900)
		//        return;
		//    if (IsTS)
		//        _columns[col].ValueDateTime = val;
		//    else
		//        _columns[col].ValueDate = val;
		//}
		//public void SetValAt(int col, DateTime val)
		//{
		//    SetValAt(col, val, false);
		//}
		//public void SetValAt(int col, bool val)
		//{
		//    if (valid(col))
		//        _columns[col].ValueBool = val;
		//}
		#endregion
		/// <summary>
        /// Attempt to get a single row from the database
        /// </summary>
        /// <param name="whereClause"></param>
        /// <returns></returns>
        public bool Select()
        {
            string bltWhereClause = "";
            DataSet ds = new DataSet(Table);
            string sql = "select * from " + Table;

			if (WhereClause == "")
			{
				bltWhereClause = _columns.LimitingValues();

				if (_andClause != "")
					bltWhereClause = AppendCondition(bltWhereClause, _andClause, " and ");

				if (m_orlist.Count > 0)
					bltWhereClause = AppendCondition(bltWhereClause, CompressList(m_orlist, " or "), " and ");

				if (bltWhereClause != "")
				{
					sql += " where " + bltWhereClause;
				}
			}
			else
				sql += " where " + WhereClause;

			if (SortBy != "")
				sql += " order by " + SortBy;

            _dt = dbi.Query(sql);
			FilterData();
			_curRow = -1;
			return MoveNext();
        }

		public bool MoveFirst()
		{
			_curRow = -1;
			return MoveNext();
		}

		public int Rows
		{
			get
			{
				if (_dt != null)
					return _dt.Rows.Count;
				else
					return 0;
			}
		}

		public bool EoF()
		{
			if (_dt == null) // 20100602 1101 BPL - Added null check
				return true;
			if (_curRow >= _dt.Rows.Count)
				return true;
			return false;
		}

		public bool HasData
		{
			get { return EoF() == false;}
		}
		/// <summary>
		/// Move to next record in result set.
		/// </summary>
		/// <returns>True if successful. False if End of Sets reached.</returns>
		public bool MoveNext()
		{
			_curRow++;

			if (EoF())
				return false;

			_columns.LoadValues(_dt.Rows[_curRow], _dt.Columns);
			//DataRow dr = _dt.Rows[_curRow];

			//foreach (DataColumn dc in _dt.Columns)
			//{
			//    _columns.AddValue(dc.ColumnName, dr[dc.ColumnName].ToString());
			//}
			return true;
		}

		/// <summary>
		/// Returns a snapshot of the current data.
		/// </summary>
		/// <returns></returns>
		public DbViewObject SnapShot()
		{
			Type ot = this.GetType();
			
			DbViewObject dvo = (DbViewObject)Activator.CreateInstance(ot);
			dvo.Columns.LoadValues(_dt.Rows[_curRow], _dt.Columns);
			//DataRow dr = _dt.Rows[_curRow];

			//foreach (DataColumn dc in _dt.Columns)
			//{
			//    dvo.Columns.AddValue(dc.ColumnName, dr[dc.ColumnName].ToString());
			//}

			return dvo;
		}

        /// <summary>
        /// Attempt to get all records from the database, or a subset according to the where clause
        /// </summary>
        /// <param name="whereClause"></param>
        /// <returns></returns>
        /// This function can use individual properties that are set to determine what the conditions 
		/// of the query is. Or a whereClause can be passed in. 
        public DataSet List(int Limit = 0)
        {
            return List(WhereClause, Limit);
        }
        public DataSet List(string whereClause, int Limit=0)
        {
            DataSet ds = new DataSet(Table);
            string sql = "select * from " + Table;
			
            if (Limit > 0)
                sql = "select top " + Limit.ToString() + " * from " + Table;

            if (whereClause == "")
            {
				if (m_andor == " or ")
					m_orlist = _columns.ListedValues();
				else
					m_andlist = _columns.ListedValues();

				if (m_andlist.Count > 0)
					whereClause = AppendCondition(whereClause, CompressList(m_andlist, " and "), " and ");

				if (m_orlist.Count > 0)
					whereClause = AppendCondition(whereClause, CompressList(m_orlist, " or "), " and ");
            }
			// if we have an AndClause or OrClause (or both) add them in as part of the query for the list. 
			if (_andClause != "")
				whereClause = AppendCondition(whereClause, _andClause, " and ");

			if (_orClause != "")
				whereClause = AppendCondition(whereClause, _orClause, " or ");

            if (whereClause != "")
                sql += " where " + whereClause;
            if (_sortby != "")
                sql += " order by " + _sortby;
			Sql = sql;
            _dt = dbi.Query(sql);
			FilterData();
            ds.Tables.Add(_dt);

            return ds;
        }

		private string CompressList(List<string> thelist, string sep=",")
		{
			string ret = "";
			string[] ra = thelist.ToArray();

			ret = String.Join(sep, ra);

			return ret;
		}

		private string AppendCondition(string where, string append, string appendwith)
		{
			if (where != "")
				where += appendwith;
			
			where += "(" + append + ")";
			return where;
		}

		public int CountRows(string whereClause)
		{
			string sql = "select count(*) as RowNo from " + Table;
			sql += " where " + whereClause;

			return CASTHelper.Integer(dbi.QueryScalar(sql));
		}

		public int CountDistinctRows(string whereClause, string fieldName)
		{
			string sql = "select distinct " + fieldName + " as NumRows from " + Table;
			sql += " where " + whereClause;

			DataTable dt = dbi.Query(sql);

			return CASTHelper.Integer(dt.Rows.Count);
		}
	}
}
